const mysql = require('../db/mysql57');
const minio = require('../db/minio');

const postDao = {
    getPostType() {
        let sql = `select id, name as text, tc, bc from PostType`;
        let data = [];
        return mysql.sqlExec(sql, data);
    },
    async addPost(uid, title, content, buffer, type) {
        // 尝试开启事务
        let sql;
        let data;
        let connection;
        let result;
        let browser = 'xlsd-treehole';
        let uri = '';
        let imgId;
        let postId;
        let ret;
        // 开启事务
        try {
            connection = await mysql.startTransaction();
        } catch (error) {
            throw error;
        }
        // 先插入动态的基本数据
        try {
            sql = `INSERT INTO Post (uid, typeId, title, content) VALUES (?, ?, ?, ?)`;
            data = [ uid, type, title, content ];
            result = await mysql.sqlExecInTransaction(connection, sql, data);
            ret = result;
            if (result.insertId) {
                postId = result.insertId;
            } else {
                await mysql.rollbackTransaction(connection);
                throw error;
            }
        } catch (error) {
            await mysql.rollbackTransaction(connection);
            throw error;
        }
        if (buffer) {
            try {
                // 往 PostImg 表中插入数据
                let sql = `insert into PostImg (browser, uri, status) VALUES (?, ?, ?)`;
                let data = [ browser, '', '待上传' ];
                result = await mysql.sqlExecInTransaction(connection, sql, data);
                imgId = result.insertId;
                uri = `${imgId}.jpg`;
                // 更新 mysql 数据库中 图片的上传状态
                sql = `update PostImg set status = ?, uri = ? where id = ?`;
                data = [ '已完成', uri, imgId ];
                await mysql.sqlExecInTransaction(connection, sql, data);
                // 更新动态表中的图片id
                sql = `update Post set imgId = ? where id = ?`;
                data = [ imgId, postId ];
                result = await mysql.sqlExecInTransaction(connection, sql, data);
            } catch (err) {
                await mysql.rollbackTransaction(connection);
                throw err;
            }
            // 往 MinIO 中上传图片（这个操作应该要封装到 minio 驱动里，后续修改）
            try {
                await new Promise((resolve, reject) => {
                    minio.minio.putObject(browser, uri, buffer, buffer.length, async function (err, etag) {
                        if (err) {
                            reject(err);
                            return ;
                        } else {
                            resolve(true);
                        }
                    });
                });
            } catch (err) {
                await mysql.rollbackTransaction(connection);
                throw err;
            }
        }
        // 提交事务
        try {
            mysql.commitTransaction(connection);
        } catch (error) {
            await mysql.rollbackTransaction(connection);
            throw error;
        }
        // 返回数据
        return ret;
    },
    addComment(uid, postId, content) {
        let sql = `insert into PostComment(uid, postId, content) values (?, ?, ?)`;
        let data = [ uid, postId, content ];
        return mysql.sqlExec(sql, data);
    },
    getPostList(uid, cursor, limit) {
        let sql;
        let data;
        if (cursor) {
            sql = `select
                    p.id as id,
                    u.id as uid,
                    u.username as username,
                    p.imgId as imgId,
                    DATE_FORMAT(p.createTime, '%Y-%m-%d %H:%i:%s') as date,
                    pt.name as type,
                    p.title as title,
                    p.content as description,
                    (select count(*) from PostLike where p.id = postId and uid = ?) as isLike,
                    (select count(*) from PostCollect where p.id = postId and uid = ?) as isCollect,
                    (select count(*) from UserLike where u.id = likeedId and uid = ?) as isFollow
                from Post as p
                left join PostType as pt on pt.id = p.typeId
                left join User as u on u.id = p.uid
                where p.id <= ? order by p.id desc limit ?`;
            data = [ uid, uid, uid, cursor, limit + 1 ];
        } else {
            sql = `select
                    p.id as id,
                    u.id as uid,
                    u.username as username,
                    p.imgId as imgId,
                    DATE_FORMAT(p.createTime, '%Y-%m-%d %H:%i:%s') as date,
                    pt.name as type,
                    p.title as title,
                    p.content as description,
                    (select count(*) from PostLike where p.id = postId and uid = ?) as isLike,
                    (select count(*) from PostCollect where p.id = postId and uid = ?) as isCollect,
                    (select count(*) from UserLike where u.id = likeedId and uid = ?) as isFollow
                from Post as p
                left join PostType as pt on pt.id = p.typeId
                left join User as u on u.id = p.uid
                order by id desc limit ?`;
            data = [ uid, uid, uid, limit + 1 ];
        }
        return mysql.sqlExec(sql, data);
    },
    getPostLikeByType(uid, typeId, cursor, limit) {
        let sql;
        let data;
        if (cursor) {
            sql = `select
                    p.id as id,
                    u.id as uid,
                    u.username as username,
                    p.imgId as imgId,
                    DATE_FORMAT(p.createTime, '%Y-%m-%d %H:%i:%s') as date,
                    pt.name as type,
                    p.title as title,
                    p.content as description,
                    (select count(*) from PostLike where p.id = postId and uid = ?) as isLike,
                    (select count(*) from PostCollect where p.id = postId and uid = ?) as isCollect,
                    (select count(*) from UserLike where u.id = likeedId and uid = ?) as isFollow
                from Post as p
                left join PostType as pt on pt.id = p.typeId
                left join User as u on u.id = p.uid
                where p.typeId = ? and p.id <= ? order by p.id desc limit ?`;
            data = [ uid, uid, uid, typeId, cursor, limit + 1 ];
        } else {
            sql = `select
                    p.id as id,
                    u.id as uid,
                    u.username as username,
                    p.imgId as imgId,
                    DATE_FORMAT(p.createTime, '%Y-%m-%d %H:%i:%s') as date,
                    pt.name as type,
                    p.title as title,
                    p.content as description,
                    (select count(*) from PostLike where p.id = postId and uid = ?) as isLike,
                    (select count(*) from PostCollect where p.id = postId and uid = ?) as isCollect,
                    (select count(*) from UserLike where u.id = likeedId and uid = ?) as isFollow
                from Post as p
                left join PostType as pt on pt.id = p.typeId
                left join User as u on u.id = p.uid
                where p.typeId = ?
                order by p.id desc limit ?`;
            data = [ uid, uid, uid, typeId, limit + 1 ];
        }
        return mysql.sqlExec(sql, data);
    },
    async getPostComment(postId, cursor, limit) {
        let sql;
        let data;
        if (cursor) {
            sql = `select
                    pc.id as id,
                    pc.uid as uid,
                    u.username as username,
                    pc.content as text,
                    DATE_FORMAT(pc.createTime, '%Y-%m-%d %H:%i:%s') as time
                from PostComment as pc
                left join User as u on u.id = pc.uid
                where pc.postId = ? and pc.id <= ?
                order by pc.id desc limit ?`;
            data = [ postId, cursor, limit + 1 ];
        } else {
            sql = `select
                    pc.id as id,
                    pc.uid as uid,
                    u.username as username,
                    pc.content as text,
                    DATE_FORMAT(pc.createTime, '%Y-%m-%d %H:%i:%s') as time
                from PostComment as pc
                left join User as u on u.id = pc.uid
                where pc.postId = ?
                order by pc.id desc limit ?`;
            data = [ postId, limit + 1 ];
        }
        return mysql.sqlExec(sql, data);
    },
    like(uid, postId, is_like) {
        let sql;
        let data;
        if (is_like) {
            sql = `insert into PostLike(uid, postId) (
                    select ?, ? where NOT EXISTS (SELECT 1 FROM PostLike WHERE uid = ? AND postId = ?)
                )`;
            data = [ uid, postId, uid, postId ];
        } else {
            sql = `delete from PostLike where uid = ? and postId = ?`;
            data = [ uid, postId ];
        }
        return mysql.sqlExec(sql, data);
    },
    collect(uid, postId, is_collect) {
        let sql;
        let data;
        if (is_collect) {
            sql = `insert into PostCollect(uid, postId) (
                    select ?, ? where NOT EXISTS (SELECT 1 FROM PostCollect WHERE uid = ? AND postId = ?)
                )`;
            data = [ uid, postId, uid, postId ];
        } else {
            sql = `delete from PostCollect where uid = ? and postId = ?`;
            data = [ uid, postId ];
        }
        return mysql.sqlExec(sql, data);
    },
    getCollectPostlist(uid, cursor, limit) {
        let sql;
        let data;
        if (cursor) {
            sql = `select
                    pc.id as collectId,
                    p.id as id,
                    u.id as uid,
                    u.username as username,
                    p.imgId as imgId,
                    DATE_FORMAT(p.createTime, '%Y-%m-%d %H:%i:%s') as date,
                    pt.name as type,
                    p.title as title,
                    p.content as description,
                    (select count(*) from PostLike where p.id = postId and uid = ?) as isLike,
                    1 as isCollect,
                    (select count(*) from UserLike where u.id = likeedId and uid = ?) as isFollow
                from PostCollect as pc
                left join Post as p on pc.postId = p.id
                left join PostType as pt on pt.id = p.typeId
                left join User as u on u.id = p.uid
                where p.uid = ? and pc.id <= ?
                order by pc.id desc
                limit ?`;
            data = [ uid, uid, uid, cursor, limit + 1 ];
        } else {
            sql = `select
                    pc.id as collectId,
                    p.id as id,
                    u.id as uid,
                    u.username as username,
                    p.imgId as imgId,
                    DATE_FORMAT(p.createTime, '%Y-%m-%d %H:%i:%s') as date,
                    pt.name as type,
                    p.title as title,
                    p.content as description,
                    (select count(*) from PostLike where p.id = postId and uid = ?) as isLike,
                    1 as isCollect,
                    (select count(*) from UserLike where u.id = likeedId and uid = ?) as isFollow
                from PostCollect as pc
                left join Post as p on pc.postId = p.id
                left join PostType as pt on pt.id = p.typeId
                left join User as u on u.id = p.uid
                where p.uid = ?
                order by pc.id desc
                limit ?`;
            data = [ uid, uid, uid, limit + 1 ];
        }
        return mysql.sqlExec(sql, data);
    },
    getPostDetail(uid, postId) {
        let sql = `select
                p.id as id,
                u.id as uid,
                u.username as username,
                p.imgId as imgId,
                DATE_FORMAT(p.createTime, '%Y-%m-%d %H:%i:%s') as date,
                pt.name as type,
                p.title as title,
                p.content as description,
                (select count(*) from PostLike where p.id = postId and uid = ?) as isLike,
                (select count(*) from PostCollect where p.id = postId and uid = ?) as isCollect,
                (select count(*) from UserLike where u.id = likeedId and uid = ?) as isFollow
            from (
                select * from Post where id = ?
            ) as p
            left join PostType as pt on pt.id = p.typeId
            left join User as u on u.id = p.uid`;
        let data = [ uid, uid, uid, postId ];
        return mysql.sqlExec(sql, data);
    },
    getUnreadCount(uid) {
        let sql = `select
            (
                select count(1)
                from PostLike as pl1
                left join Post as p1 on pl1.postId = p1.id
                where p1.uid = ? and pl1.isRead = ?
            ) as likeCount,
            (
                select count(1)
                from PostCollect as pc2
                left join Post as p2 on pc2.postId = p2.id
                where p2.uid = ? and pc2.isRead = ?
            ) as collectCount,
            (
                select count(1)
                from PostComment as pc3
                left join Post as p3 on pc3.postId = p3.id
                where p3.uid = ? and pc3.isRead = ?
            ) as commentCount`;
        let data = [ uid, 'false', uid, 'false', uid, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    likeRead(uid) {
        let sql = `UPDATE PostLike as pl
                LEFT JOIN Post as p ON pl.postId = p.id
                SET pl.isRead = 'true'
                WHERE p.uid = ?`;
        let data = [ uid ];
        return mysql.sqlExec(sql, data);
    },
    collectRead(uid) {
        let sql = `UPDATE PostCollect as pc
                LEFT JOIN Post as p ON pc.postId = p.id
                SET pc.isRead = 'true'
                WHERE p.uid = ?`;
        let data = [ uid ];
        return mysql.sqlExec(sql, data);
    },
    commentRead(uid) {
        let sql = `UPDATE PostComment as pc
                LEFT JOIN Post as p ON pc.postId = p.id
                SET pc.isRead = 'true'
                WHERE p.uid = ?`;
        let data = [ uid ];
        return mysql.sqlExec(sql, data);
    },
    getLikeListLazyLoad(uid, cursor, limit) {
        let sql, data;
        if (cursor) {
            sql = `select
                    pl.id as id,
                    pl.uid as uid,
                    u.username as username,
                    p.imgId as imgId,
                    DATE_FORMAT(pl.createTime, '%Y-%m-%d %H:%i:%s') as time
                from PostLike as pl
                left join Post as p on pl.postId = p.id
                left join User as u on pl.uid = u.id
                where pl.isRead = 'false' and p.uid = ? and pl.id <= ?
                order by pl.id desc
                limit ?`;
            data = [ uid, cursor, limit + 1 ];
        } else {
            sql = `select
                    pl.id as id,
                    pl.uid as uid,
                    u.username as username,
                    p.imgId as imgId,
                    DATE_FORMAT(pl.createTime, '%Y-%m-%d %H:%i:%s') as time
                from PostLike as pl
                left join Post as p on pl.postId = p.id
                left join User as u on pl.uid = u.id
                where pl.isRead = 'false' and p.uid = ?
                order by pl.id desc
                limit ?`;
            data = [ uid, limit + 1 ];
        }
        return mysql.sqlExec(sql, data);
    },
    getCollectListLazyLoad(uid, cursor, limit) {
        let sql, data;
        if (cursor) {
            sql = `select
                    pc.id as id,
                    pc.uid as uid,
                    u.username as username,
                    p.imgId as imgId,
                    DATE_FORMAT(pc.createTime, '%Y-%m-%d %H:%i:%s') as time
                from PostCollect as pc
                left join Post as p on pc.postId = p.id
                left join User as u on pc.uid = u.id
                where pc.isRead = 'false' and p.uid = ? and pc.id <= ?
                order by pc.id desc
                limit ?`;
            data = [ uid, cursor, limit + 1 ];
        } else {
            sql = `select
                    pc.id as id,
                    pc.uid as uid,
                    u.username as username,
                    p.imgId as imgId,
                    DATE_FORMAT(pc.createTime, '%Y-%m-%d %H:%i:%s') as time
                from PostCollect as pc
                left join Post as p on pc.postId = p.id
                left join User as u on pc.uid = u.id
                where pc.isRead = 'false' and p.uid = ?
                order by pc.id desc
                limit ?`;
            data = [ uid, limit + 1 ];
        }
        return mysql.sqlExec(sql, data);
    },
    getCommentListLazyLoad(uid, cursor, limit) {
        let sql, data;
        if (cursor) {
            sql = `select
                    pc.id as id,
                    pc.uid as uid,
                    u.username as username,
                    pc.content as content,
                    p.imgId as imgId,
                    DATE_FORMAT(pc.createTime, '%Y-%m-%d %H:%i:%s') as time
                from PostComment as pc
                left join Post as p on pc.postId = p.id
                left join User as u on pc.uid = u.id
                where pc.isRead = 'false' and p.uid = ? and pc.id <= ?
                order by pc.id desc
                limit ?`;
            data = [ uid, cursor, limit + 1 ];
        } else {
            sql = `select
                    pc.id as id,
                    pc.uid as uid,
                    u.username as username,
                    pc.content as content,
                    p.imgId as imgId,
                    DATE_FORMAT(pc.createTime, '%Y-%m-%d %H:%i:%s') as time
                from PostComment as pc
                left join Post as p on pc.postId = p.id
                left join User as u on pc.uid = u.id
                where pc.isRead = 'false' and p.uid = ?
                order by pc.id desc
                limit ?`;
            data = [ uid, limit + 1 ];
        }
        return mysql.sqlExec(sql, data);
    }
};

module.exports = postDao;